-- @owner: lihongji
-- @date: 2022-07-18
-- @testpoint: var_pop-union/union all的测试

--step1:建表；expect：成功
drop table if exists t_ustore_var_pop_case0012;
create table  t_ustore_var_pop_case0012(col_1 integer,col_2 bigint,col_3 float8,col_4 decimal(12,6),col_5 bool,col_6 char(30),
col_7 varchar2(50),col_8 varchar(30),col_9 interval day to second,col_10 timestamp,col_11 date,
col_12 smalldatetime,col_13 timestamp without time zone, col_14 blob,col_15 clob,col_16 int[])with (storage_type=ustore);
--step2:创建序列;expect:成功
drop sequence if exists s_ustore_var_pop_case0012;
create sequence s_ustore_var_pop_case0012 increment by 1 start with 10;
--step3：清空数据；expect：成功
truncate table t_ustore_var_pop_case0012;
--step4：插入数据；expect：成功
begin
	for i in 1..50 loop
      insert into t_ustore_var_pop_case0012 values(
	  i,
      s_ustore_var_pop_case0012.nextval,
	  i+445.255,
	  98*0.99*i,
	  true,
	  lpad('abc','30','@'),
	  lpad('abc','30','b'),
	  rpad('abc','30','e'),
	  (interval '4 5:12:10.222' day to second(3)),
	  to_timestamp('2019-01-03 14:58:54.000000','YYYY-MM-DD HH24:MI:SS.FFFFFF'),
	  to_timestamp('2019-01-03 14:58:54.000000','YYYY-MM-DD HH24:MI:SS.FFFFFF'),
	  to_timestamp('2019-01-03 14:58:54.000000','YYYY-MM-DD HH24:MI:SS.FFFFFF'),
	  to_timestamp('2019-01-03 14:58:54.000000','YYYY-MM-DD HH24:MI:SS.FFFFFF'),
	  lpad('10','12','01010')::blob,
	  rpad('abc','9','a@123&^%djgk'),
	  '{32,535,5645645,6767,76,67,56,48,979,978,7}'
	  );

    end loop;
end;
/
insert into t_ustore_var_pop_case0012 select * from t_ustore_var_pop_case0012;
insert into t_ustore_var_pop_case0012 select * from t_ustore_var_pop_case0012;
--step5：union/union all的测试；expect：成功
select * from (
select distinct var_pop(abs(col_1)+ceil(col_3)-floor(col_4))+var_pop(cos(col_1)-exp(col_3/100)+ln(col_4))*var_pop(sign(col_1-col_2)-sin(col_3)/floor(col_4))+var_pop(tan(col_1)+ceil(col_3)+sqrt(col_4))*var_pop(col_1+-col_2-col_4+col_3)+var_pop(col_2-col_4+col_3)+var_pop(-(-col_1-col_2-mod(col_4,col_3)))+var_pop(col_2-col_4+bitand(col_3,1)) from t_ustore_var_pop_case0012 group by col_1
union all
select var_pop(col_1) from t_ustore_var_pop_case0012
union
select var_pop(distinct col_2) from t_ustore_var_pop_case0012
minus
select var_pop(col_1) from t_ustore_var_pop_case0012) order by 1 limit 10;
--step6：清理环境；expect：成功
drop sequence if exists s_ustore_var_pop_case0012;
drop table if exists t_ustore_var_pop_case0012;
